/**
 *  Copyright (C) 2008-2013  Telosys project org. ( http://www.telosys.org/ )
 *
 *  Licensed under the GNU LESSER GENERAL PUBLIC LICENSE, Version 3.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *          http://www.gnu.org/licenses/lgpl.html
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package org.telosys.persistence.dao;

import java.util.HashMap;

import org.telosys.persistence.Query;
import org.telosys.persistence.impl.QueryImpl;
import org.telosys.persistence.syntax.StatementTranslator;

/**
 * Each instance of this class holds all the SQL requests required by an entity DAO <br>
 * to access a table.
 * 
 * There's an instance of SqlRequests in each DAO.
 * 
 * @author Laurent GUERIN
 *  
 */
public class SqlRequests<T>
{
    private final String  _table        ;

    private final String  _schema       ;

    private final String  _fullTableName  ;

    private final String  _keyColumns[] ;

    private final String  _dataColumns[] ;
    
    private final HashMap<String,String> _fieldsToColumns ;

    private final String  _sAutoIncColumn ;

    //--- SQL requests
    private final String  _sqlSelect ;

    private final String  _sqlExists ;

    private final String  _sqlInsert ;

    private final String  _sqlUpdate ;

    private final String  _sqlDelete ;

    //============================================================================
    //  CONSTRUCTORS
    //============================================================================
//    private void init(String table, String schema, String keyColumns[], String dataColumns[], HashMap<String,String> fieldsToColumns, String sAutoInc)
//    {
    /**
     * Constructs a set of SQL requests for the given table  
     * @param table  the table name
     * @param schema the schema name (can be null if not useful )
     * @param keyColumns  the columns of the Primary Key
     * @param dataColumns the other columns of the table ( not in the Primary Key ) 
     * @param fieldsToColumns  mapping "Java field name" - "Database column name"
     * @param sAutoInc the auto-incremented column ( null if none )
     */
    public SqlRequests(String table, String schema, String keyColumns[], String dataColumns[], HashMap<String,String> fieldsToColumns, String sAutoInc ) 
    {
        //--- Init the input parameters
        _table  = table.trim();

        //--- Schema
        String tmpSchema = null ;
        if ( schema != null ) {
        	tmpSchema = schema.trim();
        	if ( tmpSchema.length() == 0 ) {
        		tmpSchema = null ;
        	}
        }
        _schema = tmpSchema ;
        
        
        //--- Full table name : Schema.Table or just Table
        if ( _schema != null ) {
            _fullTableName = _schema + "." + _table ; 
        }
        else {
            _fullTableName = _table ;
        }
        
        if ( sAutoInc != null )
        {
            _sAutoIncColumn = sAutoInc.trim() ;
        }
        else
        {
            _sAutoIncColumn = null ;
        }

        _fieldsToColumns = fieldsToColumns ;
        
        _keyColumns  = (String[]) keyColumns.clone(); // clone to avoid exposing mutable object
        _dataColumns = (String[]) dataColumns.clone(); // clone to avoid exposing mutable object

        //--- Build the 4 SQL Request
        _sqlSelect = buildSqlSelect();
        _sqlExists = buildSqlSelectCount();
        _sqlInsert = buildSqlInsert();
        _sqlUpdate = buildSqlUpdate();
        _sqlDelete = buildSqlDelete();

    }
    
//    /**
//     * Constructs a set of SQL requests for the given table  
//     * @param table  the table name
//     * @param schema the schema name (can be null if not useful )
//     * @param keyColumns  the columns of the Primary Key
//     * @param dataColumns the other columns of the table ( not in the Primary Key ) 
//     * @param fieldsToColumns  mapping "Java field name" - "Database column name"
//     * @param sAutoInc the auto-incremented column
//     */
//    public SqlRequests(String table, String schema, String keyColumns[], String dataColumns[], HashMap<String,String> fieldsToColumns, String sAutoInc ) 
//    {
//        init(table, schema, keyColumns, dataColumns, fieldsToColumns, sAutoInc);
//    }
    
    //============================================================================
    //  PRIVATE METHODS
    //============================================================================
    /**
     * Determines if a column is auto-incremented
     * @param sCol
     * @return true if the given column is the auto-incremented column of the table
     */
    private boolean isAutoInc ( String sCol ) 
    {
        if ( _sAutoIncColumn != null )
        {
            return _sAutoIncColumn.equals( sCol ) ; 
        }
        return false ;
    }
    
    /**
     * Build a list of coma separated colums with ALL the columns <br>
     * (like standard columnsAll ) except for the AUTO_INC column <br> 
     * ie : "tab.col1, tab.col2, tab.col3"
     * @param bPrefix
     * @return
     */
    private String columnsWithoutAutoInc() 
    {
        StringBuffer sb = new StringBuffer(200);
        int iNbCol = 0;
        //--- Data Columns
        for ( int c = 0 ; c < _dataColumns.length ; c++ )
        {
            if ( ! isAutoInc ( _dataColumns[c] ) ) // Check if the column is auto inc
            {        
	            if (iNbCol > 0)
	            {
	                sb.append(", ");
	            }
	            sb.append(_dataColumns[c]);
	            iNbCol++;
            }
        }
        //--- Key Columns
        for ( int c = 0 ; c < _keyColumns.length ; c++ )
        {
            if ( ! isAutoInc ( _keyColumns[c] ) ) // Check if the column is auto inc
            {        
	            if (iNbCol > 0)
	            {
	                sb.append(", ");
	            }
	            sb.append(_keyColumns[c]);
	            iNbCol++;	            
            }
        }
        return sb.toString();
    }
    
    /**
     * Build a list of coma separated colums (for ALL the columns) ie : "tab.col1, tab.col2, tab.col3"
     * 
     * @return
     */
    private String columnsAll()
    {
        StringBuffer sb = new StringBuffer(200);
        int iNbCol = 0;

        //--- Data Columns
        for ( int c = 0 ; c < _dataColumns.length ; c++ )
        {
            if (iNbCol > 0)
            {
                sb.append(", ");
            }
            sb.append(_dataColumns[c]);
            iNbCol++;
        }

        //--- Key Columns
        for ( int c = 0 ; c < _keyColumns.length ; c++ )
        {
            if (iNbCol > 0)
            {
                sb.append(", ");
            }
            sb.append(_keyColumns[c]);
            iNbCol++;
        }
        return sb.toString();
    }

    /**
     * Build a list of coma separated "?" ( for ALL the columns ) ie : "?, ?, ?"
     * 
     * @return
     */
    private String values()
    {
        StringBuffer sb = new StringBuffer(200);
        int iNbCol = 0;

        //--- Data Columns
        for ( int c = 0 ; c < _dataColumns.length ; c++ )
        {
            if (iNbCol > 0)
            {
                sb.append(", ");
            }
            sb.append("?");
            iNbCol++;
        }

        //--- Key Columns
        for ( int c = 0 ; c < _keyColumns.length ; c++ )
        {
            if (iNbCol > 0)
            {
                sb.append(", ");
            }
            sb.append("?");
            iNbCol++;
        }
        return sb.toString();
    }

    private String valuesWithoutAutoInc() 
    {
        int iNbCol = _dataColumns.length + _keyColumns.length ;
        //--- AutoInc = 1 col less
        if ( iNbCol > 0 ) iNbCol-- ;
        
        StringBuffer sb = new StringBuffer(200);
        //--- Columns
        for ( int c = 0 ; c < iNbCol ; c++ )
        {
            if (c > 0)
            {
                sb.append(", ");
            }
            sb.append("?");
        }
        return sb.toString();
    }

    /**
     * Build the where criteria ( for KEY columns only) <br>
     * ie : "tab.col1 = ? and tab.col2 = ?"
     * 
     * @return
     */
    private String whereCriteria() 
    {
        StringBuffer sb = new StringBuffer(200);
        //--- Key Columns
        for ( int c = 0 ; c < _keyColumns.length ; c++ )
        {
            if (c > 0)
            {
                sb.append(" and ");
            }
            sb.append(_keyColumns[c] + " = ?");
        }
        return sb.toString();
    }

    /**
     * Build the set column value clause (for DATA columns only) ie : "tab.col1 = ?, tab.col2 = ?"
     * 
     * @return
     */
    private String setDataValues()
    {
        StringBuffer sb = new StringBuffer(200);
        //--- Data Columns
        for ( int c = 0 ; c < _dataColumns.length ; c++ )
        {
            if (c > 0)
            {
                sb.append(", ");
            }
            sb.append(_dataColumns[c] + " = ?");
        }
        return sb.toString();
    }

    /**
     * Build the SQL SELECT request
     * 
     * @return
     */
    private String buildSqlSelect()
    {
        return "select " + columnsAll() + " from " + _fullTableName + " where " + whereCriteria(); 
    }

    /**
     * Build the SQL COUNT request
     * 
     * @return
     */
    private String buildSqlSelectCount()
    {
        return "select count(*) from " + _fullTableName + " where " + whereCriteria(); 
    }

    /**
     * Build the SQL INSERT request
     * 
     * @return
     */
    private String buildSqlInsert()
    {
        if ( _sAutoIncColumn != null )
        {
            //--- Special INSERT request without the AUTO-INC column
            return "insert into " + _fullTableName + " ( " + columnsWithoutAutoInc() + " ) values ( " + valuesWithoutAutoInc() + " )"; 
        }
        else
        {
            //--- Standard INSERT request with all the columns
            return "insert into " + _fullTableName + " ( " + columnsAll() + " ) values ( " + values() + " )"; 
        }
    }

    /**
     * Build the SQL UPDATE request
     * 
     * @return
     */
    private String buildSqlUpdate()
    {
        return "update " + _fullTableName + " set " + setDataValues() + " where " + whereCriteria(); 
    }

    /**
     * Build the SQL DELETE request
     * 
     * @return
     */
    private String buildSqlDelete()
    {
        return "delete from " + _fullTableName + " where " + whereCriteria();
    }

    //============================================================================
    /**
     * Creates a Query 
     * @param iParamCount number of parameters for the query ( the number of '?' in the string : 0 to N )
     * @param sQueryCriteria the SQL criteria for the WHERE clause
     * @param sQueryEndOfSelect the additional SQL clause ( e.g. : "order by ..." )
     * @return
     */
    public Query<T> createQuery(Class<T> entityBeanClass, int iParamCount, String sQueryCriteria, String sQueryEndOfSelect) //throws TelosysException
    {
    	//--- Translate Java field names to Database column names
   		StatementTranslator sqlTranslator = new StatementTranslator( this._fieldsToColumns );
    	String sqlQueryCriteria    = sqlTranslator.toSQL(sQueryCriteria);
    	String sqlQueryEndOfSelect = sqlTranslator.toSQL(sQueryEndOfSelect);
    	
        //--- Create the SQL QUERY
        return createQueryWithoutJoin(entityBeanClass, iParamCount, sqlQueryCriteria, sqlQueryEndOfSelect); 
    }

    private Query<T> createQueryWithoutJoin(Class<T> entityBeanClass, int iParamCount, String sQueryCriteria, String sQueryEndOfSelect)
    {
        String sWhere = SqlBuilder.buildSqlWhere(sQueryCriteria);
        String sEnd   = SqlBuilder.buildSqlComplement(sQueryEndOfSelect);

        String sSelect = "select " + columnsAll() + " from " + _fullTableName + sWhere + sEnd; 
        String sCount  = "select count(*) from " + _fullTableName + sWhere;
        String sDelete = "delete from " + _fullTableName + sWhere;
        
        return new QueryImpl<T>(entityBeanClass, iParamCount, sSelect, sCount, sDelete);
    }

    //============================================================================
    //  GETTERS
    //============================================================================

    /**
     * Returns the SQL SELECT request
     * 
     * @return the SQL Select clause
     */
    public String getSqlSelect()
    {
        return _sqlSelect;
    }

    /**
     * Returns the SQL SELECT request adding the optional clause at the end
     * 
     * @param sOptionalClause
     * @return the SQL Select clause
     */
    public String getSqlSelect(String sOptionalClause)
    {
        if (sOptionalClause != null)
        {
            return _sqlSelect + " " + sOptionalClause;
        }
        else
        {
            return _sqlSelect;
        }
    }

    /**
     * Returns the SQL SELECT COUNT request
     * 
     * @return
     */
    public String getSqlExists()
    {
        return _sqlExists;
    }

    /**
     * Returns the SQL INSERT request
     * 
     * @return
     */
    public String getSqlInsert()
    {
        return _sqlInsert;
    }

    /**
     * Returns the SQL UPDATE request
     * 
     * @return
     */
    public String getSqlUpdate()
    {
        return _sqlUpdate;
    }

    /**
     * Returns the SQL DELETE request
     * 
     * @return
     */
    public String getSqlDelete()
    {
        return _sqlDelete;
    }

    /**
     * Returns the TABLE NAME
     * 
     * @return
     */
    public String getTable()
    {
        return _table;
    }

    /**
     * Returns the SCHEMA NAME if any
     * 
     * @return the schema name (or null if none)
     */
    public String getSchema()
    {
        return _schema ;
    }

    /**
     * Returns the "full table name" ( table name with schema if any )
     * 
     * @return the full name 
     */
    public String getFullTableName()
    {
        return _fullTableName ;
    }

    /**
     * Returns the 'Auto-Inc' column if any (or NULL)
     * @return
     */
    public String getAutoIncColumn()
    {
        return _sAutoIncColumn;
    }

    /**
     * Returns true if the table has an 'Auto-Inc' column
     * @return
     */
    public boolean hasAutoIncColumn()
    {
        return _sAutoIncColumn != null ;
    }

    /* (non-Javadoc)
     * @see java.lang.Object#toString()
     */
    public String toString()
    {
        return "'"+ _fullTableName + "' requests (auto-incr column = " + _sAutoIncColumn + ") \n"
        + ". " + _sqlSelect + "\n"
        + ". " + _sqlInsert + "\n"
        + ". " + _sqlUpdate + "\n"
        + ". " + _sqlDelete + "\n"
        + ". " + _sqlExists + "\n"
        ;
    }
}